package com.imicode.hotel.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang3.StringUtils;

import com.imicode.hotel.dao.FoodTypeDao;
import com.imicode.hotel.utils.Dbutils;
import com.imicode.hotel.vo.FoodType;

public class FoodTypeDbutilsImpl implements FoodTypeDao {
	
	final static String ADDSQL = "insert into foodtype(typeName) values(?)";
	final static String LISTSQL = "select id,typeName from foodtype";
	final static String SEARCHSQL = "select id,typeName from foodtype where typeName like %?%";
	final static String GETSQL = "select id,typeName from foodtype where id=?";
	final static String EDITSQL = "update foodtype set typeName=? where id=?";
	final static String DELETESQL = "delete from foodtype where id=?";

	@Override
	public void save(String name) {
		Connection conn = null;
		try {
			//1. 获取连接
			conn = Dbutils.getConnection();
			//2. 执行sql
			QueryRunner qr = new QueryRunner();
			qr.update(conn, ADDSQL, name);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//3. 关闭资源
			DbUtils.closeQuietly(conn);
		}
	}

	@Override
	public List<FoodType> list(String name) {
		Connection conn = null;
		List<FoodType> list = null;
		try {
			//1. 获取连接 
			conn = Dbutils.getConnection();
			//2. 执行sql
			QueryRunner qr = new QueryRunner();
			if(StringUtils.isEmpty(name)) {
				list = qr.query(conn, LISTSQL, new BeanListHandler<FoodType>(FoodType.class));
			} else {
				list = qr.query(conn, SEARCHSQL, new BeanListHandler<FoodType>(FoodType.class), name);
			}
						
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//3. 关闭资源
			DbUtils.closeQuietly(conn);
		}
		return list;
	}

	@Override
	public FoodType get(int id) {
		Connection conn = null;
		FoodType foodType = null;
		try {
			//1. 获取连接
			conn = Dbutils.getConnection();
			//2. 执行sql
			QueryRunner qr = new QueryRunner();
			foodType = qr.query(conn, GETSQL, new BeanHandler<>(FoodType.class), id);			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//3. 关闭资源
			DbUtils.closeQuietly(conn);
		}
		return foodType;
	}

	@Override
	public void edit(int id, String name) {
		Connection conn = null;
		try {
			//1. 获取连接
			conn = Dbutils.getConnection();
			//2. 执行sql
			QueryRunner qr = new QueryRunner();
			qr.update(conn, EDITSQL, name, id);			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//3. 关闭资源
			DbUtils.closeQuietly(conn);
		}
	}

	@Override
	public void delete(int id) {
		Connection conn = null;
		try {
			//1. 获取连接
			conn = Dbutils.getConnection();
			//2. 执行sql
			QueryRunner qr = new QueryRunner();
			qr.update(conn, DELETESQL, id);			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//3. 关闭资源
			DbUtils.closeQuietly(conn);
		}
	}

}
